﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using LdExpress;
using System.Data;
using MySql.Data.MySqlClient;
using System.Globalization;
using System.IO;

public partial class Cusdoc : System.Web.UI.Page
{
    public string ContainerPage = "./Cusdoc.aspx";
    public string strPager = "";
    public string strCondition = "";
    public int NumOfItemsPerPage = 20;
    public string strError = "";
    public string strMess = "";
    public int nPermissions = 0;
    public string strLetter = "letter";
    public string strAlphaBet = "";
    string strChar = "";
    public static string strTotalGroupContType = "";
    public string Province = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["logined_id"] != null)
        {
            if (Session["cusdoc"] != "") nPermissions = int.Parse(Session["cusdoc"].ToString());
            if (Session["superadmin"] != "" && int.Parse(Session["superadmin"].ToString()) > 0)
                nPermissions = 10;

        }
        else
            Response.Redirect("Login.aspx");
        if (IsPostBack)
            return;
        if (Session["doclist_province"] == null)
            Session["doclist_province"] = "0";
        Tinh objTinh = new Tinh();
        if (int.Parse(Session["doclist_province"].ToString()) > 0)
            objTinh.ID = long.Parse(Session["doclist_province"].ToString());
        else
            objTinh.ID = long.Parse(Session["UserProvince"].ToString());
        objTinh.Load(GV.cn, GV.trans);
        Province = objTinh.Ten.ToUpper();
        loadDropdownProvince();

        if (Session["cusdocfrom"] == null)
            Session["cusdocfrom"] = DateTime.Now.AddMonths(-2).ToString("dd-MM-yyyy"); //new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString("dd-MM-yyyy");
        if (Session["cusdocto"] == null)
            Session["cusdocto"] = DateTime.Now.ToString("dd-MM-yyyy");
        if (Session["cusdockeyword"] != null)
            keyword.Text = Session["cusdockeyword"].ToString();
        if (Session["cusdocfindtype"] != null)
            ddType.SelectedValue = Session["cusdocfindtype"].ToString();
        if (Session["CusDocWarning"] != null)
            ddCusWarning.SelectedValue = Session["CusDocWarning"].ToString();
        if (Session["cusdocfrom"] != null)
            txtFrom.Text = Session["cusdocfrom"].ToString();
        if (Session["cusdocto"] != null)
            txtTo.Text = Session["cusdocto"].ToString();
        if (Session["cusdocPOL"] != null)
            txtSearchPOL.Text = Session["cusdocPOL"].ToString();
        if (Session["cusdocDEST"] != null)
            txtSearchDEST.Text = Session["cusdocDEST"].ToString();
        //Delete Doc
        if (Request.QueryString["action"] != null && Request.QueryString["ID"] != null && nPermissions > 3)
        {
            CusdocModel obj = new CusdocModel();
            obj.ID = long.Parse(Request.QueryString["ID"].ToString());
            obj.Load(GV.cn, GV.trans);
            obj.Delete(GV.cn, GV.trans);
        }
        #region Build List
        int nNumOfPages = 0;
        int nPage = 1;
        string strPageID = "page_number";
        try
        {

            if (Request.QueryString[strPageID] != null)
                nPage = int.Parse(Request.QueryString[strPageID]);
        }
        catch
        {
            nPage = 1;
        }
        if (nPage <= 0) nPage = 1;
        DataTable dtList;
        bool TotalGroupContType_Display = false;
        strCondition = GetCondittion(true, out TotalGroupContType_Display);
        strTotalGroupContType = "";
        if (TotalGroupContType_Display)
            strTotalGroupContType = ShippingDetail.GetTotalGroupCatContType(strCondition);
        if ((Request.QueryString["action"] != null) && Request.QueryString["action"].ToString() == "export")
        {
            BuildList(strCondition, -1, -1, out nNumOfPages, out dtList, null, null);
        }
        else
        {
            BuildList(strCondition, NumOfItemsPerPage, nPage, out nNumOfPages, out dtList, null, null);
        }
        if (nPage > nNumOfPages) nPage = 1;

        rptData.DataSource = dtList;
        rptData.DataBind();
        #endregion
        #region Build Pager
        CommonFunctions.BuildPager(Request, nNumOfPages, nPage, strPageID, ContainerPage, out strPager);
        #endregion

        //Export to Excel
        if ((Request.QueryString["action"] != null) && Request.QueryString["action"].ToString() == "export")
        {
            Response.Clear(); //this clears the Response of any headers or previous output
            Response.Buffer = true; //make sure that the entire output is rendered simultaneously

            ///Set content type to MS Excel sheet
            ///Use "application/vnd.ms-word" for MS Word doc files
            ///"application/pdf" for PDF files
            ///"application/vnd.ms-excel" for MS Excel files


            Response.ContentType = "application/vnd.ms-excel";
            //Response.ContentType = "application/vnd.ms-word";
            Response.AddHeader("content-disposition", "attachment; filename = CusDoc" + DateTime.Now.ToString("dd_MM_yyyy") + ".xls");

            StringWriter stringWriter = new StringWriter(); //System.IO namespace should be used

            HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

            ///
            ///Render the entire Page control in the HtmlTextWriter object
            ///We can render individual controls also, like a DataGrid to be
            ///exported in custom format (excel, word etc)
            ///
            this.RenderControl(htmlTextWriter);
            Response.Write(stringWriter.ToString());
            Response.End();
        }
    }

    private void BuildList(string strCondition, int NumOfItemsPerPage, int PageNo, out int nNumOfPages, out DataTable dtList, MySqlConnection cn, MySqlTransaction trans)
    {
        DataSet ds = null;
        dtList = new DataTable();
        dtList.Columns.Add("ID");
        dtList.Columns.Add("INDEX");
        dtList.Columns.Add("FILENO");
        dtList.Columns.Add("MBKGNO");
        dtList.Columns.Add("BILLTYPE"); //Loai Bill
        dtList.Columns.Add("VOL"); //SO LOAI CONT
        dtList.Columns.Add("POLNAME"); //11 pol
        dtList.Columns.Add("DESTNAME"); // 14 DESTNAME
        dtList.Columns.Add("SHONBOARD");// 12. SH.ON BOARD
        dtList.Columns.Add("ETA");// EDIABLED 2. Cột 'ETA": sẽ  link dữ liệu cột 40 bên bảng nhập liệu bill ( nếu cột 38 Status chọn là ETA, Cột 39 Location chọn cảng chùng với cảng tại cột 14. DEST thì được hiển là hàng đã đến cảng đích) như vậy dữ liệu sẽ link sang cột này. Cho phép tự điền tay vào cột này được.
        dtList.Columns.Add("HBLNO");
        dtList.Columns.Add("MBLNO");
        //3. Cột Shipper, Côt Cnee
        //Nếu ô loại bill chỉ chọn duy nhất ô Ori.MBL hoặc ô Sur.MBL ( mà không chọn 2 ô còn lại) thì lúc này :
        //Ô SHPR = 31. MBL SHPR
        //Ô CNEE = 32. MBL CNEE
        //Nếu ô loại bill có chọn 1 trong 2 loại sau: Ori.HBL hoặc Sur.HBL thì lúc này :
        //Ô SHPR = 5. HBL SHPR
        //Ô Cnee = 6. HBL CNEE
        dtList.Columns.Add("CUST");
        dtList.Columns.Add("SHPR");
        dtList.Columns.Add("CNEE");

        dtList.Columns.Add("CARRCODE"); //30. CARR. N.
        dtList.Columns.Add("CTRNAME"); //37. CTR.NAME
        dtList.Columns.Add("TOP");//24. T.O.P
        dtList.Columns.Add("STATUS");
        dtList.Columns.Add("NOTE");
        dtList.Columns.Add("REPORT");
        dtList.Columns.Add("ACTION");

        //Get List by SoIDs
        nNumOfPages = 0;
        CusdocModel obj = new CusdocModel();
        CusdocModel.GetList(strCondition, "FILENO desc", NumOfItemsPerPage, PageNo, out nNumOfPages, out ds, GV.cn, GV.trans);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            int count = 0;
            if (Request.QueryString["page_number"] != null && Request.QueryString["page_number"].ToString() != "")
            {
                int pagenumber = 1;
                try
                {
                    pagenumber = int.Parse(Request.QueryString["page_number"]);
                    count = NumOfItemsPerPage * (pagenumber - 1);
                }
                catch { count = 0; }
            }
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                count++;
                string ID = dr["ID"].ToString();
                string INDEX = count.ToString();
                string FILENO = dr["FILENO"].ToString();
                string MBKGNO = dr["MBKGNO"].ToString();
                string BILLTYPE = "";
                switch (dr["MBLType"].ToString())
                {
                    case "1":
                        BILLTYPE = CusdocModel.OriMBL.ToString();
                        break;
                    case "2":
                        BILLTYPE = CusdocModel.SurMBL.ToString();
                        break;
                }
                switch (dr["HBLType"].ToString())
                {
                    case "1":
                        if (BILLTYPE.Length > 0) BILLTYPE += " / ";
                        BILLTYPE += CusdocModel.OriHBL.ToString();
                        break;
                    case "2":
                        if (BILLTYPE.Length > 0) BILLTYPE += " / ";
                        BILLTYPE += CusdocModel.SurHBL.ToString();
                        break;
                }
                string VOL = ShippingDetail.GetGroupCatContType(long.Parse(ID));
                string POLNAME = dr["POLNAME"].ToString();
                string PODNAME = dr["PODNAME"].ToString();
                string DESTNAME = dr["DESTNAME"].ToString();
                string SHONBOARD = dr["SHONBOARD"].ToString();
                int bWarning = 0;
                if (SHONBOARD.Length > 0)
                {
                    string ETDClass = "";
                    if (Tracking.NotificationETD(long.Parse(ID)) > 0)
                    {
                        ETDClass = "red";
                        bWarning = 1;
                    }
                    SHONBOARD = "<span class=\"" + ETDClass + "\">" + DateTime.Parse(SHONBOARD).ToString("MMM-dd-yyyy") + "</span>";
                }
                string ETA = Tracking.GetETA(long.Parse(ID));
                if (!string.IsNullOrEmpty(ETA))
                {
                    string ETAClass = "";
                    if (Tracking.NotificationETA(long.Parse(ID)) > 0)
                    {
                        ETAClass = "red";
                        bWarning = 1;
                    }
                    if (DateTime.Parse(ETA) > DateTime.MinValue)
                        ETA = "<span class=\"" + ETAClass + "\">" + DateTime.Parse(ETA).ToString("MMM-dd-yyyy") + "</span>";
                    else
                        ETA = "TBA";
                }
                //2. Cột 'ETA": sẽ  link dữ liệu cột 40 bên bảng nhập liệu bill ( nếu cột 38 Status chọn là ETA, Cột 39 Location chọn cảng chùng với cảng tại cột 14. DEST thì được hiển là hàng đã đến cảng đích) như vậy dữ liệu sẽ link sang cột này. Cho phép tự điền tay vào cột này được.
                string HBLNO = dr["HBLNO"].ToString();
                string MBLNO = dr["MBLNO"].ToString();
                string CUST = "";
                string SHPR = dr["HBLSHPR"].ToString();
                if (SHPR.Length > 0 && (SHPR.IndexOf("\n") > 0))
                {
                    SHPR = SHPR.Substring(0, SHPR.IndexOf("\n") + 1);
                }
                string CNEE = dr["HBLCNEE"].ToString();
                if (CNEE.Length > 0 && (CNEE.IndexOf("\n") > 0))
                {
                    CNEE = CNEE.Substring(0, CNEE.IndexOf("\n") + 1);
                }
                if (POLNAME.ToLower().IndexOf("vietnam") >= 0)
                    CUST = SHPR;
                else if (PODNAME.ToLower().IndexOf("vietnam") >= 0)
                    CUST = CNEE;
                string CARRCODE = dr["CARRCODE"].ToString();
                string CTRNAME = dr["CTRNAME"].ToString();
                string TOP = dr["TOP"].ToString();
                string STATUS = "";
                if (ETA.Trim().Length > 0)
                {
                    STATUS = "Arrival: Reporting";
                }
                else if (Tracking.GetATD1(long.Parse(ID), GV.cn, GV.trans) > 0)
                {
                    STATUS = "T/S report: Reporting";
                }
                else if (MBLNO.Length > 0)
                {
                    STATUS = "B/L: Confirming ";
                }
                else if (dr["DOGMN"].ToString().Length > 0)
                {
                    STATUS = "B/L: Drafting";
                }
                else if (ShippingDetail.SIStatusSending(long.Parse(ID)) > 0)// Cont/ SEAL input
                {
                    STATUS = "SI: Sending";
                }
                else if (dr["MBKGNO"].ToString().Length > 0)
                {
                    STATUS = "SI: Tasking";
                }
                string CostStatus = CusdocModel.GetStatusCostingSale(long.Parse(FILENO), GV.cn, GV.trans);
                STATUS += "<br>" + CostStatus;
                string NOTE = dr["NOTE"].ToString();
                //string HBLNTF = dr["HBLNTF"].ToString();
                string ACTION = "";
                string REPORT = "";
                if (nPermissions >= 3)
                {
                    ACTION = "<a href=\"javascript:doViewBill('" + ID + "','View')\">View</a>";
                    ACTION += " - <a href=\"javascript:doEdit('" + ID + "','" + FILENO + "')\">Edit</a>";
                    REPORT = "<a href=\"javascript:doOpenDocReport('" + ID + "','shippinginstruction')\"> Instruction </a>";
                    REPORT += " - <a href=\"javascript:doOpenDocReport('" + ID + "','arrialreport')\"> Arrial </a>";
                }
                if (Session["transhipmentreport"] != null && int.Parse(Session["transhipmentreport"].ToString()) > 0)
                    REPORT += "- <a href=\"javascript:doOpenDocReport('" + ID + "','transhipmentreport')\"> Transhipment </a>";
                if (nPermissions > 7)
                    ACTION += " - <a href=\"javascript:doDelete('" + ID + "','" + FILENO + "')\">Delete</a>";
                bool bDisplay = false;
                if (Session["CusDocWarning"] != null)
                {
                    if (Session["CusDocWarning"].ToString() != "-1")
                    {
                        if (int.Parse(Session["CusDocWarning"].ToString()) == bWarning) bDisplay = true;
                    }
                    else
                        bDisplay = true;
                }
                else
                    bDisplay = true;

                if (bDisplay)
                {
                    string[] strValues = new string[] { ID, INDEX,FILENO, MBKGNO,  BILLTYPE,VOL,POLNAME,DESTNAME,SHONBOARD,ETA, HBLNO,MBLNO,
                   CUST, SHPR,CNEE,CARRCODE,CTRNAME,TOP,STATUS,NOTE,REPORT, ACTION};
                    dtList.Rows.Add(strValues);
                }

            }
        }


    }

    protected void btnAddNew_ServerClick(object sender, EventArgs e)
    {
        Response.Redirect("CreateCusDoc.aspx");
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        if (keyword.Text.Length > 0)
        {
            Session["cusdockeyword"] = keyword.Text;
        }
        else
            Session["cusdockeyword"] = "";
        Session["cusdocfindtype"] = ddType.SelectedValue;
        Session["cusdocPOL"] = txtSearchPOL.Text;
        Session["cusdocDEST"] = txtSearchDEST.Text;
        if (txtFrom.Text.ToLower() != "from")
            Session["cusdocfrom"] = txtFrom.Text;
        else
            Session["cusdocfrom"] = "";

        if (txtTo.Text.ToLower() != "to")
            Session["cusdocto"] = txtTo.Text;
        else
            Session["cusdocto"] = "";
        Response.Redirect("cusdoc.aspx");
    }
    protected string GetCondittion(bool list, out bool TotalGroupContType_Display)
    {
        bool display = false;
        strCondition = "";
        if (list)
        {
            if (Session["doclist_province"] != null && int.Parse(Session["doclist_province"].ToString()) > 0)
                strCondition += " AND IdProvince=" + Session["doclist_province"].ToString();
            else if (nPermissions <= 7)
                strCondition += " AND IdProvince =" + Session["UserProvince"].ToString();
        }
        if (Session["cusdocPOL"] != null && Session["cusdocPOL"].ToString() != "")
        {
            strCondition += " AND POLNAME like '%" + DBModule.RefineString(Session["cusdocPOL"].ToString()) + "%'";
            display = true;
        }
        if (Session["cusdocDEST"] != null && Session["cusdocDEST"].ToString() != "")
        {
            strCondition += " AND DESTNAME like '%" + DBModule.RefineString(Session["cusdocDEST"].ToString()) + "%'";
            display = true;
        }
        if (Session["cusdockeyword"] != null && Session["cusdockeyword"].ToString() != "")
        {
            strCondition += " AND SUBSTRING_INDEX(" + Session["cusdocfindtype"].ToString() + ",'\n',1) like '%" + DBModule.RefineString(Session["cusdockeyword"].ToString()) + "%'";
            display = true;
        }

        if (Session["cusdocfrom"] != null && Session["cusdocfrom"].ToString() != "")
        {
            DateTime dt = DateTime.ParseExact(Session["cusdocfrom"].ToString(), "dd-MM-yyyy",
                                  CultureInfo.InvariantCulture);
            dt = new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0);
            strCondition += " AND SHONBOARD>='" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'";
        }
        if (Session["cusdocto"] != null && Session["cusdocto"].ToString() != "")
        {
            DateTime dt = DateTime.ParseExact(Session["cusdocto"].ToString(), "dd-MM-yyyy",
                                  CultureInfo.InvariantCulture);
            dt = new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0);
            strCondition += " AND SHONBOARD<='" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'";
        }
        if (Session["cusdocfrom"] != null && Session["cusdocfrom"].ToString() != "" && Session["cusdocto"] != null && Session["cusdocto"].ToString() != "")
            display = true;
        TotalGroupContType_Display = display;
        return strCondition;

    }
    protected void loadDropdownProvince()
    {
        string sql = "";
        DataSet ds = null;
        sql = "SELECT DISTINCT  tbl_tinh.Ten ,tbl_tinh.ID FROM tbl_cost_accounting,tbl_tinh WHERE tbl_cost_accounting.IdProvince=tbl_tinh.ID";
        //if (nPermissions <= 7)
        //    sql += " AND tbl_tinh.ID in (" + Session["UserLocation"].ToString() + ")";
        ds = DBModule.ExecuteQuery(sql, GV.cn, GV.trans);
        DataTable dtb = ds.Tables[0];
        ddProvince.DataSource = dtb;
        ddProvince.DataTextField = dtb.Columns[0].ToString();
        ddProvince.DataValueField = dtb.Columns[1].ToString();
        ddProvince.DataBind();
        ddProvince.Items.Insert(0, new ListItem("Select", "0"));
        if (Session["doclist_province"] != null)
        {
            if (ddProvince.Items.FindByValue(Session["doclist_province"].ToString()) != null)
                ddProvince.SelectedValue = Session["doclist_province"].ToString();
        }
    }
    protected void ddProvince_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["doclist_province"] = ddProvince.SelectedValue;
        Response.Redirect("cusdoc.aspx");
    }
    protected void ddCusWarning_Selected(object sender, EventArgs e)
    {
        Session["CusDocWarning"] = ddCusWarning.SelectedValue.ToString();
        Response.Redirect("cusdoc.aspx");
    }
}